Skip to main content

Build Dashboard

Untangled Finance used Metabase as BI tool for visulization

1. Create your first query​

How to write a query to find out the monthly $ gas spent for your wallet

Embark on your analytical adventure with us! In this guide, we provide a detailed walkthrough to craft your inaugural query, revealing your wallet’s monthly gas spent in USD.

Getting Started​

Initiate a New Query: Begin by selecting “Create New Query”. Start laying the basic structure of your query.

SELECT 
FROM 
WHERE
GROUP BY

Choosing the Right Table​

Determine Your Data Source: Instead of delving into the intricate raw blockchain tables to fetch gas expenditures for your wallet, utilize the pre-built, analytics-ready Spell table named gas.fees. This table facilitates an effortless aggregation of gas expenses across eight EVM chains specifically for your wallet.

SELECT 
FROM gas.fees
WHERE
GROUP BY

Crafting Your Query​

Identify Your Columns: Once you’ve zeroed in on the right table, pinpoint the tx_fee_usd column to track USD gas expenditures. To specify the wallet you’re examining, use the tx_sender field. For demonstration purposes, we’ll be using Vitalik’s wallet (0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045).

Aggregation & Time Period: Our goal is to get monthly gas spendings, so we need to truncate the block_date to month and applying the SUM() function, then group by each month go derive the results.

SELECT
  DATE_TRUNC('month', block_date) AS period,
  SUM(tx_fee_usd) AS monthly_gas_spent,
  SUM(SUM(tx_fee_usd)) OVER (ORDER BY DATE_TRUNC('month', block_date)) AS cumulative_gas_spent
FROM gas.fees
WHERE
  tx_sender = 0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045 -- vitalik.eth 
GROUP BY
  1
ORDER BY
  1

Boost Flexibility with Parameters​

In the example, you’ll see tx_sender = \{\{Your Wallet Address}} -- default is vitalik.eth. This denotes the use of the parameter feature, enhancing the query’s flexibility. To mark a field as a parameter, wrap it in "". Alternatively, activate the “Add Parameter” button.

SELECT
  DATE_TRUNC('month', block_date) AS period,
  SUM(tx_fee_usd) AS monthly_gas_spent,
  SUM(SUM(tx_fee_usd)) OVER (ORDER BY DATE_TRUNC('month', block_date)) AS cumulative_gas_spent
FROM gas.fees
WHERE
  tx_sender = {{Your Wallet Address}} -- default is vitalik.eth
GROUP BY
  DATE_TRUNC('month', block_date)
ORDER BY
  1

Execute & Fetch the Result​

All set! Click “Run” and behold the outcome. Kudos on crafting your first query! 🎉

Now that you have successfully run your query, you can go ahead and save it so you can keep monitoring your gas usage going forward. To save, click Save in the upper right part of the screen.

2. Create your first visualization​

How to create a combination bar chart and line chart to visualize the monthly $ gas spent for your wallet

  • Begin Visualization: Click on “New Dashboard” and select your desired chart type. For this tutorial, initiate with a bar chart and click “Add Dashboard.”
  • Display Data: By default, the chart will showcase monthly_gas_spent. To augment the depth of insights, include the cumulative_gas_spent column.
  • Adjust Chart Type: Transition the representation of cumulative_gas_spent from a bar to a line chart, offering a more nuanced view.
  • Fine-tune Your Chart: Customize the display values for a polished look. For detailed formatting guidance, refer to this resource.